﻿using INTEX.DataBase;
using System;
using System.Data;
using System.Windows;
using System.Data.SQLite;
using System.Collections;
using System.Collections.Generic;
using System.Linq;
using System.Threading;
using ReflectionStudio.Core.Events;
namespace INTEX.DataBase.Tools
{
    public class SQLiteDatabase
    {
        String dbConnection;
        string sql = "";
        SQLiteConnection cnn;
        public SQLiteDatabase()
        {
            dbConnection = "Data Source=C:/IntexReport/Data/MVTEXAPP/APP.s3db";
            cnn = new SQLiteConnection(dbConnection);

        }
        public void Close()
        {
            cnn.Close();
            SQLiteConnection.ClearPool(cnn);//在连接池中，关闭指定的数据文件（不影响主数据库） 
            GC.Collect();
            System.Threading.Thread.Sleep(10); 
        }

        public SQLiteDatabase(String inputFile)
        {

            dbConnection = String.Format("Data Source={0}", inputFile); 
            cnn = new SQLiteConnection(dbConnection);
        }
        public bool CheckTable(string tbName)
        {
            sql = "SELECT COUNT(*) as CNT FROM sqlite_master where type='table' and name='" + tbName + "'";
            DataTable dt = GetDataTable(sql);
            if (dt.Rows[0][0].ToString().Trim() == "1")
            {
                return true;
            }
            if (dt.Rows[0][0].ToString().Trim() == "0")
            {
                return false;
            }
            return false;

        }
        public void CreateICC_EXART()
        {
            sql = "CREATE TABLE ICC_EXART (ID INTEGER, ARTICLE,DESCRIPTION,TWIDTH,VWIDTH,GSQM,GM,YARN,PRICE,SPRICE,COMPOSITION,REMARK, PRIMARY KEY(ID ASC))";
            ExecuteNonQuery(sql);
        }
        public void CreateICC_MAXORD()
        {
            sql = "CREATE TABLE ICC_MAXORD (ID INTEGER, MAXORD, PRIMARY KEY(ID ASC))";
            ExecuteNonQuery(sql);
            sql = "INSERT INTO ICC_MAXORD (MAXORD) VALUES (1)";
            ExecuteNonQuery(sql);

        }
        public void CreateICC_EXORD()
        {
            sql = "CREATE TABLE ICC_EXORD (ID INTEGER, VKPAUFKRS,VKPAUFLFD,VKPAUFPOS,ITEM,VKPART,VKPARTDES,VKPFARBE,VKPCDES,TWIDTH,VWIDTH,GSQM,GM,YARN,PRICE,SPRICE,COMPOSITION,ORDTYPE,QUANTITY,CUSTOMER,VKPNEUDAT,VKPNEUTIME,VKPNUEBEN,REMARK,NUMBER1,NUMBER2,NUMBER3,NUMBER4,NUMBER5,CHAR1,CHAR2,CHAR3,CHAR4,CHAR5, PRIMARY KEY(ID ASC))";
            ExecuteNonQuery(sql);
        }

        public DataTable GetDataTable(string sql)
        {

            DataTable dt = new DataTable();

            try
            {
                SQLiteConnection cnn = new SQLiteConnection(dbConnection);
                cnn.Open();
                SQLiteCommand mycommand = new SQLiteCommand(cnn);
                mycommand.CommandText = sql;
                SQLiteDataReader reader = mycommand.ExecuteReader();
                dt.Load(reader);
                reader.Close();
                cnn.Close();
            }
            catch (Exception ex)
            {
                Tracer.Error(ex.StackTrace, ex);
            }
            return dt;
        }
        public DataTable GetDataTable(string sql, IList<SQLiteParameter> cmdparams)
        {

            DataTable dt = new DataTable();
            try
            {
                SQLiteConnection cnn = new SQLiteConnection(dbConnection);
                cnn.Open();
                SQLiteCommand mycommand = new SQLiteCommand(cnn);
                mycommand.CommandText = sql;
                mycommand.Parameters.AddRange(cmdparams.ToArray());
                mycommand.CommandTimeout = 180;
                SQLiteDataReader reader = mycommand.ExecuteReader();
                dt.Load(reader);
                reader.Close();
                cnn.Close();
            }

            catch (Exception ex)
            {
                Tracer.Error(ex.StackTrace, ex);
            }
            return dt;

        }
        /// <summary>
        ///     Allows the programmer to interact with the database for purposes other than a uery.
        /// </summary>
        /// <param name="sql">The SQL to be run.</param>
        /// <returns>An Integer containing the number of rows updated.</returns>
        public bool ExecuteNonQuery(string sql)
        {
            bool successState = false;
            cnn.Open();
            using (SQLiteTransaction mytrans = cnn.BeginTransaction())
            {
                SQLiteCommand mycommand = new SQLiteCommand(sql, cnn);
                try
                {
                    mycommand.CommandTimeout = 180;
                    mycommand.ExecuteNonQuery();
                    mytrans.Commit();
                    successState = true;
                    cnn.Close();
                }
                catch (Exception e)
                {
                    mytrans.Rollback();
                }
                finally
                {
                    mycommand.Dispose();
                    cnn.Close();
                }
            }
            return successState;
        }
        public bool ExecuteNonQuery(string sql, IList<SQLiteParameter> cmdparams)
        {
            bool successState = false;
            cnn.Open();
            using (SQLiteTransaction mytrans = cnn.BeginTransaction())
            {
                SQLiteCommand mycommand = new SQLiteCommand(sql, cnn, mytrans);
                try
                {
                    mycommand.Parameters.AddRange(cmdparams.ToArray());
                    mycommand.CommandTimeout = 180;
                    mycommand.ExecuteNonQuery();
                    mytrans.Commit();
                    successState = true;
                    cnn.Close();
                }
                catch (Exception e)
                {
                    mytrans.Rollback();
                    throw e;
                }
                finally
                {
                    mycommand.Dispose();
                    cnn.Close();
                }
            }
            return successState;
        }
        public void InsertSQLiteTable(DataRow dwData)
        {
            DataTable tb = dwData.Table;
            string insert = "INSERT INTO " + tb.TableName + " (";
            string values = " VALUES (";
            string commandtext = "";
            cnn.Open();
            using (SQLiteTransaction mytrans = cnn.BeginTransaction())
            {
                SQLiteCommand mycommand = new SQLiteCommand(sql, cnn, mytrans);
                try
                {
                    foreach (DataColumn field in dwData.Table.Columns)
                    {
                        string name = field.ColumnName;
                        if (name == "ROWID")
                            name = "ROWNID";
                        insert += name + ",";
                        values += ":" + name + ",";
                        SQLiteParameter pm = new SQLiteParameter();
                        pm.ParameterName = name;
                        pm.Value = dwData[name].ToString();
                        mycommand.Parameters.Add(pm);
                    }

                    insert += "***";
                    values += "***";
                    commandtext = insert.Replace(",***", ")") + values.Replace(",***", ")");
                    mycommand.CommandTimeout = 180;
                    mycommand.CommandText = commandtext;
                    mycommand.ExecuteNonQuery();
                    mytrans.Commit();
                    cnn.Close();
                }
                catch (Exception e)
                {
                    mytrans.Rollback();
                    throw e;
                }
                finally
                {
                    mycommand.Dispose();
                    cnn.Close();
                }
            }
        }

        /// <summary>
        ///     Allows the programmer to easily update rows in the DB.
        /// </summary>
        /// <param name="tableName">The table to update.</param>
        /// <param name="data">A dictionary containing Column names and their new values.</param>
        /// <param name="where">The where clause for the update statement.</param>
        /// <returns>A boolean true or false to signify success or failure.</returns>
        public bool Update(String tableName, Dictionary<String, String> data, String where)
        {
            String vals = "";
            Boolean returnCode = true;
            if (data.Count >= 1)
            {
                foreach (KeyValuePair<String, String> val in data)
                {
                    vals += String.Format(" {0} = '{1}',", val.Key.ToString(), val.Value.ToString());
                }
                vals = vals.Substring(0, vals.Length - 1);
            }
            try
            {
                this.ExecuteNonQuery(String.Format("update {0} set {1} where {2};", tableName, vals, where));
            }
            catch
            {
                returnCode = false;
            }
            return returnCode;
        }
    }
}
